from typing import List, Optional, Dict, Any
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_
from app.models.ai_workflow import AIWorkflow, WorkflowStep, ErrorHistory, TaskStatus, StepStatus
from app.schemas.ai_workflow import AIWorkflowCreate, WorkflowStepCreate
from datetime import datetime


def check_workflow_completion(result_data: dict, expected_outputs: dict) -> dict:
    """检查工作流完成情况"""
    completion_details = {}
    missing_outputs = []
    total_expected = 0
    total_actual = 0
    
    for output_type, expected_count in expected_outputs.items():
        actual_count = 0
        
        if output_type == "optimized_prompts":
            actual_count = 1 if result_data.get("optimized_prompt") else 0
        elif output_type == "mj_images":
            mj_urls = result_data.get("mj_image_urls") or []
            actual_count = len(mj_urls)
        elif output_type == "split_images":
            split_images = result_data.get("split_images") or []
            actual_count = len(split_images)
        elif output_type == "flux_variations":
            flux_vars = result_data.get("flux_variations") or []
            actual_count = len(flux_vars)
        elif output_type == "product_info_items":
            product_info = result_data.get("product_info") or {}
            actual_count = sum(1 for v in product_info.values() if v is not None)
        elif output_type == "detail_pages":
            # 修正：应该查找detail_pages而不是detail_page
            detail_pages = result_data.get("detail_pages") or []
            actual_count = len(detail_pages)
        
        completion_details[output_type] = {
            "expected": expected_count,
            "actual": actual_count,
            "complete": actual_count >= expected_count
        }
        
        if actual_count < expected_count:
            missing_outputs.append({
                "type": output_type,
                "expected": expected_count,
                "actual": actual_count,
                "missing": expected_count - actual_count
            })
        
        total_expected += expected_count
        total_actual += actual_count
    
    completion_rate = (total_actual / total_expected * 100) if total_expected > 0 else 0
    
    return {
        "is_complete": len(missing_outputs) == 0,
        "completion_rate": round(completion_rate, 2),
        "completion_details": completion_details,
        "missing_outputs": missing_outputs,
        "total_expected": total_expected,
        "total_actual": total_actual
    }


class CRUDAIWorkflow:
    def create_workflow(self, db: Session, *, obj_in: AIWorkflowCreate, user_id: int) -> AIWorkflow:
        """创建AI工作流"""
        db_obj = AIWorkflow(
            user_id=user_id,
            task_name=obj_in.task_name,
            input_data=obj_in.input_data,
            task_config=obj_in.task_config,
            execution_summary=obj_in.execution_summary,
            expected_outputs=obj_in.expected_outputs,
            status=TaskStatus.PENDING
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        
        # 创建默认步骤
        if obj_in.steps:
            for step_data in obj_in.steps:
                step = WorkflowStep(
                    workflow_id=db_obj.id,
                    step_name=step_data.step_name,
                    step_order=step_data.step_order,
                    api_provider=step_data.api_provider,
                    input_data=step_data.input_data,
                    status=StepStatus.PENDING
                )
                db.add(step)
        
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    def get_workflow(self, db: Session, workflow_id: int) -> Optional[AIWorkflow]:
        """获取工作流详情"""
        return db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
    
    def get_user_workflows(self, db: Session, user_id: int, skip: int = 0, limit: int = 100) -> List[AIWorkflow]:
        """获取用户的工作流列表"""
        return db.query(AIWorkflow).filter(AIWorkflow.user_id == user_id).offset(skip).limit(limit).all()
    
    def update_workflow_status(self, db: Session, workflow_id: int, status: TaskStatus, 
                              current_step: Optional[str] = None, progress: Optional[int] = None,
                              error_message: Optional[str] = None) -> Optional[AIWorkflow]:
        """更新工作流状态"""
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj:
            db_obj.status = status
            if current_step is not None:
                db_obj.current_step = current_step
            if progress is not None:
                db_obj.progress = progress
            if error_message is not None:
                db_obj.error_message = error_message
            if status == TaskStatus.RUNNING and not db_obj.started_at:
                db_obj.started_at = datetime.utcnow()
            elif status in [TaskStatus.COMPLETED, TaskStatus.FAILED, TaskStatus.CANCELLED]:
                db_obj.completed_at = datetime.utcnow()
            
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def update_workflow_result(self, db: Session, workflow_id: int, result_data: Dict[str, Any]) -> Optional[AIWorkflow]:
        """更新工作流结果"""
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj:
            db_obj.result_data = result_data
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def delete_workflow(self, db: Session, workflow_id: int) -> bool:
        """删除工作流"""
        from app.models.image import ImageRecord  # 导入ImageRecord模型
        from app.models.product import Product  # 导入Product模型
        
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj:
            try:
                # 1. 将products表中引用该workflow_id的记录的workflow_id设为NULL
                db.query(Product).filter(Product.workflow_id == workflow_id).update({Product.workflow_id: None})
                
                # 2. 删除相关的image记录（通过group_id关联）
                group_id = f'workflow_{workflow_id}'
                db.query(ImageRecord).filter(ImageRecord.group_id == group_id).delete()
                
                # 3. 删除相关的错误历史记录
                db.query(ErrorHistory).filter(ErrorHistory.workflow_id == workflow_id).delete()
                
                # 4. 删除相关的工作流步骤（这也会级联删除步骤相关的错误记录）
                db.query(WorkflowStep).filter(WorkflowStep.workflow_id == workflow_id).delete()
                
                # 5. 最后删除工作流本身
                db.delete(db_obj)
                db.commit()
                return True
            except Exception as e:
                db.rollback()
                raise e
        return False
    
    def update_workflow_expected_outputs(self, db: Session, workflow_id: int, expected_outputs: Dict[str, Any]) -> Optional[AIWorkflow]:
        """更新工作流预期输出"""
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj:
            db_obj.expected_outputs = expected_outputs
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def update_execution_summary(self, db: Session, workflow_id: int, execution_summary: Dict[str, Any]) -> Optional[AIWorkflow]:
        """更新执行摘要"""
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj:
            db_obj.execution_summary = execution_summary
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def update_task_config(self, db: Session, workflow_id: int, task_config: Dict[str, Any]) -> Optional[AIWorkflow]:
        """更新任务配置"""
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj:
            db_obj.task_config = task_config
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def increment_execution_summary_step(self, db: Session, workflow_id: int, step_name: str, output_type: str, count: int = 1) -> Optional[AIWorkflow]:
        """增量更新执行摘要"""
        db_obj = db.query(AIWorkflow).filter(AIWorkflow.id == workflow_id).first()
        if db_obj and db_obj.execution_summary:
            summary = db_obj.execution_summary.copy()
            
            # 更新步骤完成数
            summary['steps_completed'] = summary.get('steps_completed', 0) + 1
            summary['last_successful_step'] = step_name
            
            # 更新输出统计
            if 'outputs_generated' not in summary:
                summary['outputs_generated'] = {}
            summary['outputs_generated'][output_type] = summary['outputs_generated'].get(output_type, 0) + count
            
            db_obj.execution_summary = summary
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def get_user_task_statistics(self, db: Session, user_id: int) -> Dict[str, Any]:
        """获取用户任务统计"""
        from sqlalchemy import func
        
        # 按状态统计任务数量
        status_stats = db.query(
            AIWorkflow.status,
            func.count(AIWorkflow.id).label('count')
        ).filter(
            AIWorkflow.user_id == user_id
        ).group_by(AIWorkflow.status).all()
        
        # 计算完成度统计
        completed_tasks = db.query(AIWorkflow).filter(
            AIWorkflow.user_id == user_id,
            AIWorkflow.status == TaskStatus.COMPLETED
        ).all()
        
        partial_completed = 0
        fully_completed = 0
        
        for task in completed_tasks:
            if task.expected_outputs and task.result_data:
                completion_status = check_workflow_completion(
                    task.result_data, task.expected_outputs
                )
                if completion_status["is_complete"]:
                    fully_completed += 1
                else:
                    partial_completed += 1
        
        return {
            "status_breakdown": {status: count for status, count in status_stats},
            "completion_stats": {
                "fully_completed": fully_completed,
                "partial_completed": partial_completed,
                "total_completed": len(completed_tasks)
            },
            "total_tasks": sum(count for _, count in status_stats)
        }
    
    def get_user_workflows_by_status(self, db: Session, user_id: int, status: TaskStatus, 
                                   skip: int = 0, limit: int = 100) -> List[AIWorkflow]:
        """按状态获取用户工作流列表"""
        return db.query(AIWorkflow).filter(
            AIWorkflow.user_id == user_id,
            AIWorkflow.status == status
        ).offset(skip).limit(limit).all()
    
    def get_user_task_summary(self, db: Session, user_id: int) -> Dict[str, Any]:
        """获取用户任务摘要"""
        total = db.query(AIWorkflow).filter(AIWorkflow.user_id == user_id).count()
        pending = db.query(AIWorkflow).filter(
            AIWorkflow.user_id == user_id,
            AIWorkflow.status == TaskStatus.PENDING
        ).count()
        running = db.query(AIWorkflow).filter(
            AIWorkflow.user_id == user_id,
            AIWorkflow.status == TaskStatus.RUNNING
        ).count()
        completed = db.query(AIWorkflow).filter(
            AIWorkflow.user_id == user_id,
            AIWorkflow.status == TaskStatus.COMPLETED
        ).count()
        failed = db.query(AIWorkflow).filter(
            AIWorkflow.user_id == user_id,
            AIWorkflow.status == TaskStatus.FAILED
        ).count()
        
        return {
            "total_tasks": total,
            "pending_tasks": pending,
            "running_tasks": running,
            "completed_tasks": completed,
            "failed_tasks": failed,
            "success_rate": round((completed / total * 100), 2) if total > 0 else 0
        }


class CRUDWorkflowStep:
    def create_step(self, db: Session, *, workflow_id: int, step_data: WorkflowStepCreate) -> WorkflowStep:
        """创建工作流步骤"""
        db_obj = WorkflowStep(
            workflow_id=workflow_id,
            step_name=step_data.step_name,
            step_order=step_data.step_order,
            api_provider=step_data.api_provider,
            input_data=step_data.input_data,
            status=StepStatus.PENDING
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    def get_workflow_steps(self, db: Session, workflow_id: int) -> List[WorkflowStep]:
        """获取工作流的所有步骤"""
        return db.query(WorkflowStep).filter(WorkflowStep.workflow_id == workflow_id).order_by(WorkflowStep.step_order).all()
    
    def get_step(self, db: Session, step_id: int) -> Optional[WorkflowStep]:
        """获取单个步骤"""
        return db.query(WorkflowStep).filter(WorkflowStep.id == step_id).first()
    
    def update_step_status(self, db: Session, step_id: int, status: StepStatus,
                          external_task_id: Optional[str] = None,
                          external_conversation_id: Optional[str] = None,
                          output_data: Optional[Dict[str, Any]] = None,
                          error_message: Optional[str] = None) -> Optional[WorkflowStep]:
        """更新步骤状态"""
        db_obj = db.query(WorkflowStep).filter(WorkflowStep.id == step_id).first()
        if db_obj:
            db_obj.status = status
            if external_task_id is not None:
                db_obj.external_task_id = external_task_id
            if external_conversation_id is not None:
                db_obj.external_conversation_id = external_conversation_id
            if output_data is not None:
                db_obj.output_data = output_data
            if error_message is not None:
                db_obj.error_message = error_message
            if status == StepStatus.RUNNING and not db_obj.started_at:
                db_obj.started_at = datetime.utcnow()
            elif status in [StepStatus.COMPLETED, StepStatus.FAILED, StepStatus.SKIPPED]:
                db_obj.completed_at = datetime.utcnow()
            
            db.commit()
            db.refresh(db_obj)
        return db_obj
    
    def get_pending_steps(self, db: Session) -> List[WorkflowStep]:
        """获取等待执行的步骤"""
        return db.query(WorkflowStep).filter(
            WorkflowStep.status.in_([StepStatus.PENDING, StepStatus.WAITING])
        ).all()


class CRUDErrorHistory:
    def create_error(self, db: Session, *, user_id: int, user_name: str, error_message: str,
                    workflow_id: Optional[int] = None, step_id: Optional[int] = None,
                    error_type: Optional[str] = None, api_provider: Optional[str] = None) -> ErrorHistory:
        """创建错误记录"""
        db_obj = ErrorHistory(
            user_id=user_id,
            workflow_id=workflow_id,
            step_id=step_id,
            error_type=error_type,
            error_message=error_message,
            api_provider=api_provider,
            user_name=user_name
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    def get_user_errors(self, db: Session, user_id: int, skip: int = 0, limit: int = 100) -> List[ErrorHistory]:
        """获取用户错误历史"""
        return db.query(ErrorHistory).filter(ErrorHistory.user_id == user_id).offset(skip).limit(limit).all()
    
    def add_error_history(self, db: Session, workflow_id: int, step: str, error_message: str) -> ErrorHistory:
        """添加错误历史记录"""
        # 获取工作流信息
        workflow = self.get_workflow(db, workflow_id)
        if not workflow:
            raise Exception(f"工作流 {workflow_id} 不存在")
        
        # 创建错误记录
        db_obj = ErrorHistory(
            user_id=workflow.user_id,
            workflow_id=workflow_id,
            step_id=None,  # 如果需要可以传入step_id
            error_type="workflow_step_error",
            error_message=f"[{step}] {error_message}",
            api_provider="MJ",  # 根据实际情况调整
            user_name=""  # 如果需要可以从用户表获取
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj


# 创建CRUD实例
ai_workflow = CRUDAIWorkflow()
workflow_step = CRUDWorkflowStep()
error_history = CRUDErrorHistory()